﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;

namespace Skyworth.CRM.DAL
{
    public class EmployeeDAL : SuperDataBase, IEmployeeDAL
    {
        public DataTable Query(string strWhere)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select EmpId,EmpName,DeptID,DepartmentName,JobTitle from T_BasicData_Employee as tbe left join");
                strSql.Append(" T_BasicData_Department as tbd on tbe.DeptID=tbd.DepartmentId where 1=1");
                strSql.Append(strWhere);
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                return DB.ExecuteDataSet(cmd).Tables[0];
            }
            catch (Exception ex)
            {                
                throw ex;
            }            
        }
        public DataTable GetEmployeesByPage(string strWhere,string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
      {
           recordCount = 0;
           pageCount = 0;
          StringBuilder strCommandText = new StringBuilder();
          DataSet ds ;
          try
          {
              strCommandText.AppendFormat(
             "SELECT row_number() over({0}) AS rownum, EmpId, EmpName, EmpCode, tbe.DeptID, tbe.FBRID, JobTitle, Email, Phone,FBankName,FBankNumber, ParentEmpID,",strOrderBy);
              strCommandText.Append(
                  " Status,tbd.DepartmentName ,case Status when 0 then '停用'else '启用' end as StatusName FROM T_BasicData_Employee as tbe");
              strCommandText.Append(" left join T_BasicData_Department as tbd on tbe.DeptId=tbd.DepartmentId");
              // strCommandText.Append(" left join T_BasicData_Department as tbdBR on tbe.FBRID=tbdBR.DepartmentId");
              strCommandText.Append(strWhere);

              System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
              DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
              DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
              DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
              DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
              DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
              DB.AddOutParameter(sqlcommand, "@pTotalRecords",DbType.Int32,4);
              DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
              ds = DB.ExecuteDataSet(sqlcommand);
             recordCount= Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
             pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));
          }
          catch (Exception)
          {
              
              throw;
          }
          return ds.Tables[0];
      }

        public void DeleteEmployee(int employeeId)
        {
            string sql = "update T_BasicData_Employee set Status=0 where EmpId=@empId";
            DbCommand dbCommand = DB.GetSqlStringCommand(sql);
            DB.AddInParameter(dbCommand,"@empId",DbType.Int64,employeeId);
            DB.ExecuteNonQuery(dbCommand);
        }

        public Employee GetEmployeeById(int employeeId)
        {
            Employee model = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 EmpId,EmpName,EmpCode,DeptID,FBRID,JobTitle,Email,phone,FBankName,FBankNumber,parentEmpID,Status from T_BasicData_Employee ");
            strSql.Append(" where EmpId=@empId");
            DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
            DB.AddInParameter(cmd, "@empId", DbType.Int32, employeeId);
            using (IDataReader dr = DB.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    model = new Employee();
                    if (dr["EmpId"].ToString() != "")
                    {
                        model.EmpId = int.Parse(dr["EmpId"].ToString());
                    }
                    model.EmpName = dr["EmpName"].ToString();
                    model.EmpCode = dr["EmpCode"].ToString();
                    model.Email = dr["Email"].ToString();
                    int depeId = 0;
                    int.TryParse(dr["DeptID"].ToString(), out depeId);
                    model.DeptId = depeId;
                    int brID = 0;
                    int.TryParse(dr["FBRID"].ToString(), out brID);
                    model.BrId = brID;

                    model.JobTitle = dr["JobTitle"].ToString();

                    int parentId = 0;
                    int.TryParse(dr["ParentEmpID"].ToString(), out parentId);
                    model.ParentEmpId = parentId;

                    Boolean status = true;
                    bool.TryParse(dr["Status"].ToString(), out status);
                    model.Status = status;

                    model.Phone = dr["Phone"].ToString();
                    model.BankName = dr["FBankName"].ToString();
                    model.BankNumber = dr["FBankNumber"].ToString();
                }
            }
            return model;
        }

        public void Add(Employee employee)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_BasicData_Employee(");
            strSql.Append("EmpName,EmpCode,DeptID,FBRID,JobTitle,Email,Phone,ParentEmpID,FBankName,FBankNumber,Status)");
            strSql.Append(" values (");
            strSql.Append("@empName,@empCode,@deptId,@fbrId,@jobTitle,@email,@phone,@parentId,@bankName,@bankNumber,@status) ");
            DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
            DB.AddInParameter(cmd, "@empName", DbType.String, employee.EmpName);
            DB.AddInParameter(cmd, "@empCode", DbType.String, employee.EmpCode);
            DB.AddInParameter(cmd, "@deptId", DbType.Int32, employee.DeptId);
            DB.AddInParameter(cmd, "@fbrId", DbType.Int16, employee.BrId);
            DB.AddInParameter(cmd, "@jobTitle", DbType.String, employee.JobTitle);
            DB.AddInParameter(cmd, "@email", DbType.String, employee.Email);
            DB.AddInParameter(cmd, "@phone", DbType.String, employee.Phone);
            DB.AddInParameter(cmd, "@parentId", DbType.Int32, employee.ParentEmpId);
            DB.AddInParameter(cmd, "@bankName", DbType.String, employee.BankName);
            DB.AddInParameter(cmd, "@bankNumber", DbType.String, employee.BankNumber);
            DB.AddInParameter(cmd, "@status", DbType.Boolean, employee.Status);
            DB.ExecuteNonQuery(cmd);
         }

        public void Update(Employee employee)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_BasicData_Employee set ");
            strSql.Append("EmpName=@empName,");
            strSql.Append("EmpCode=@empCode,");
            strSql.Append("DeptID=@deptId,");
            strSql.Append("FBRID=@fbrId,");
            strSql.Append("JobTitle=@jobTitle,");
            strSql.Append("Email=@email,");
            strSql.Append("Phone=@phone,");
            strSql.Append("ParentEmpID=@parentId, ");
            strSql.Append("Status=@status,");
            strSql.Append("FBankName=@bankName,");
            strSql.Append("FBankNumber=@bankNumber");
            strSql.Append(" where EmpID=@empId");
            DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
            DB.AddInParameter(cmd, "@empName", DbType.String, employee.EmpName);
            DB.AddInParameter(cmd, "@empCode", DbType.String, employee.EmpCode);
            DB.AddInParameter(cmd, "@deptId", DbType.Int32, employee.DeptId);
            DB.AddInParameter(cmd, "@fbrId", DbType.Int16, employee.BrId);
            DB.AddInParameter(cmd, "@jobTitle", DbType.String, employee.JobTitle);
            DB.AddInParameter(cmd, "@email", DbType.String, employee.Email);
            DB.AddInParameter(cmd, "@phone", DbType.String,employee.Phone);
            DB.AddInParameter(cmd, "@parentId", DbType.Int32, employee.ParentEmpId);
            DB.AddInParameter(cmd, "@status", DbType.Boolean, employee.Status);
            DB.AddInParameter(cmd, "@empId", DbType.Int32, employee.EmpId);
            DB.AddInParameter(cmd, "@bankName", DbType.String, employee.BankName);
            DB.AddInParameter(cmd, "@bankNumber", DbType.String, employee.BankNumber);
            DB.ExecuteNonQuery(cmd);
        }
    }
}